package bp.demo;

import java.util.Hashtable;

public class SQLApp {
	public SQLApp() {
	}

	public static String Gener_App(String mark, Hashtable ht) throws Exception {
		String key = null;
		if (ht != null && ht.containsKey("Key") == true)
			key = ht.get("Key").toString();
		// 产品规范管理
		if (mark.startsWith("App_Product_Plan") == true) {
			return productPlan(mark, ht);
		}
		// // 需求清单管理
		// if (mark.startsWith("App_ProductRequirementList") == true) {
		// return productPlan(mark, ht);
		// }
		// 需求清单管理
		if (mark.startsWith("App_ProductRequirementList") == true) {
			return productPlan(mark, ht);
		}

		// 审核计划管理
		if (mark.startsWith("App_ProductExaminePlan") == true) {
			return productPlan(mark, ht);
		}

		// 售后客户信息 bjf
		if (mark.startsWith("App_After_Customer") == true) {
			return productPlan(mark, ht);
		}

		// 产品类型 bjf
		if (mark.startsWith("App_Product_Category") == true) {
			return productPlan(mark, ht);
		}

		// // 问题报表
		// if (mark.startsWith("App_DataV_QMSSH_Dept") == true) {
		// return App_DataV_QMSSH(mark, ht);
		// }
		// //各个部门的总数量
		// if (mark.startsWith("App_DataV_QMSSH_Dept_Count") == true) {
		// return App_DataV_QMSSH(mark, ht);
		// }
		// //各个部门的重复数量
		// if (mark.startsWith("App_DataV_QMSSH_Dept_Repeat_Count") == true) {
		// return App_DataV_QMSSH(mark, ht);
		// }
		// //各个部门的平均值
		// if (mark.startsWith("App_DataV_QMSSH_Dept_Avg") == true) {
		// return App_DataV_QMSSH(mark, ht);
		// }
		// 获取人员信息
		if (mark.startsWith("App_User_Info") == true) {
			return productPlan(mark, ht);
		}
		String sql = "";
		if (mark.startsWith("App_QmsAuditOfficer") == true) {
			// 过程审核
			if (ht.get("leixing").equals("0")) {
				sql = "SELECT empno as No,empname as Name FROM qms_qiatypeemp WHERE QIATypeNo='02' ";
			}
			// 体系审核
			else {
				sql = "SELECT empno as No,empname as Name FROM qms_qiatypeemp WHERE QIATypeNo='01' ";
			}
			return sql;
		}
		// 绑定未开始的计划
		if (mark.startsWith("App_QmsAuditPlanId") == true) {
			// 过程审核
			if (ht.get("leixing").equals("0")) {
				sql = "SELECT MyPK AS No,JiHuaDanHao AS Name,ND as 年度,YF as 月份,SQL_KeHuT as 客户名称,ChanPinLeiXingT as 产品类型,ChanPinJiXing as 产品机型,ShenHeBiaoZhunT as 审核标准,SQL_ShenHeYuanT as 审核员  FROM qms_plan WHERE plansta='0' order by rdt desc";
			} else if (ht.get("leixing").equals("1")) {
				// 体系审核且为内部
				sql = "SELECT MyPK AS No,JiHuaDanHao AS Name,ND as 年度,YF as 月份, case NeiWaiBuShenHe when 0 then '内部' else '外部' end  as 内外部审核,ShenHeTiXiT as 审核体系,SQL_TiXiShenHeYuanT as 审核员 FROM QMS_SystemPlan WHERE plansta='0' and NeiWaiBuShenHe = '0'  order by rdt desc";
			} else if (ht.get("leixing").equals("2")) {
				// 体系审核且为外部
				sql = "SELECT MyPK AS No,JiHuaDanHao AS Name,ND as 年度,YF as 月份, case NeiWaiBuShenHe when 0 then '内部' else '外部' end  as 内外部审核,ShenHeTiXiT as 审核体系,SQL_TiXiShenHeYuanT as 审核员 FROM QMS_SystemPlan WHERE plansta='0' and NeiWaiBuShenHe = '1' order by rdt desc";
			} else {
				// 外部审核的类型
				sql = "SELECT MyPK AS No,JiHuaDanHao AS Name,SQR as 创建人,NianDu as 年度,KeHu as 客户,WaiShenShenHeLeiXing as 审核类型,ChanPinLeiXing as 产品类型 FROM QMS_ExtAuditPlan WHERE plansta='0' order by rdt desc";
			}
			return sql;
		}
		if (mark.startsWith("App_QmsAuditDeptManagerId") == true) {
			// 获取部门经理
			sql = "SELECT DISTINCT a.No,a.Name FROM port_emp a, port_deptempstation c WHERE a.No = c.FK_Emp AND c.FK_Dept = '" + key + "'";
			return sql;
		}
		if (mark.startsWith("App_QmsWTSortId") == true) {
			// 获取2级
			sql = "SELECT No,Name FROM qms_wtsort2 WHERE Sort1='" + key + "'";
			return sql;
		}
		if (mark.startsWith("App_Qms8DId") == true) {
			// 获取8d
			sql = "SELECT BillNo AS No,BillNo AS Name,KeHuTouSuShiJian AS 客户投诉日期,KeHu AS 客户,KeHuJiXing AS 客户机型,GongSiXingHao AS 公司型号 FROM frm_d1biaodan";
			return sql;
		}
		// if (mark.startsWith("App_QmsAuditSystemId") == true) {
		// //获取体系管理员和代理体系管理员
		// sql = "SELECT a.No,a.Name FROM port_emp a, port_deptempstation c WHERE a.No = c.FK_Emp AND c.FK_Dept = '1005'";
		// return sql;
		// }
		if (mark.startsWith("App_QmsPlanIdFull") == true) {
			// 过程审核
			if (ht.get("LeiXing").equals("0"))
				sql = "select SQL_KeHu as Customer,SQL_KeHuT as CustomerT,ChanPinLeiXing as ProType,ChanPinLeiXingT as ProTypeT,ChanPinJiXing as Product FROM qms_plan where mypk='" + key + "'";
			else
				sql = "select '' as Customer,'' as CustomerT,'' as ProType,'' as ProTypeT,'' as Product FROM qms_plan where mypk='" + key + "'";
			return sql;
		}
		// 获取人员信息
		if (mark.startsWith("App_RenYuan") == true) {
			return "select a.No,a.Name, c.Name as stationName from port_emp a ,port_deptempstation b ,port_station c  where a.no= b.FK_Emp and b.FK_Station = c.No";
		}

		// 获取8D
		if (mark.startsWith("App_QmsAudiKM8D") == true) {
			sql = "SELECT BillNo AS No,BillNo as Name,KeHuTouSuShiJian as 客户投诉时间,KeHu AS 客户,KeHuJiXing as 客户机型,GongSiXingHao as 公司型号 FROM frm_d1biaodan";
			return sql;
		}
		// 获取任务
		if (mark.startsWith("App_QmsTaskId") == true) {
			sql = "select MyPK AS No,TaskID as Name, CASE ShenHeLeiXing WHEN '0' THEN '过程内审' WHEN '1' THEN '体系内审' WHEN '2' THEN '三方审核' WHEN '3' THEN '潜供方审核' WHEN '4' THEN '年度供方审核' WHEN '5' THEN '飞行检查' WHEN '6' THEN '体系审核' ELSE '未知类型' END as 审核类型,CustomerT as 客户,ProTypeT AS 产品型号,AuditOfficerT as 审核负责人,DTFrom as 开始时间 from QMS_Task";
			return sql;
		}
		// 行动项页面获取管理评审
		if (mark.startsWith("App_QmsGlps") == true) {
			sql = "SELECT OID AS No,YeWuDanHao AS Name,NianDu as 年度,PingShenHuiYiShiJian as 评审时间,PingShenHuiYiDiDian as 评审地点,pe.Name as 申请人 FROM nd16rpt ,port_emp pe WHERE WFState = 3 and SQR=pe.No  ORDER BY RDT DESC";
			return sql;
		}
		throw new Exception("err@没有判断的SQL标记:" + mark);
	}

	public static String productPlan(String mark, Hashtable ht) throws Exception {
		String key = null;
		if (ht != null && ht.containsKey("Key") == true) {
			key = ht.get("Key").toString();
		}

		switch (mark) {
		// 产品规范管理 级联赋值
		case "App_Product_Plan":
			return "SELECT No,LJH as 零件号,LJMC as 零件名称,KHBM AS 客户号,KHMC AS 客户描述,KHLJH AS 客户物料编码 FROM QMS_Part";
		case "App_Product_PlanFull":
			return "select LJH, LJMC,KHBM,KHMC,KHLJH from QMS_Part where No = '" + key + "'";

		// 需求清单管理 级联赋值
		case "App_ProductRequirementList":
			return "SELECT distinct No,KHBM as 客户编码,KHMC as 客户名称, LJH as 零件号,LJMC as 零件名称,KHLJH AS 客户物料编码 FROM QMS_Part";
		case "App_ProductRequirementListFull":
			return "select KHBM, KHMC, LJH, LJMC, KHLJH from QMS_Part where No = '" + key + "'";

		// 审核计划管理 级联赋值
		case "App_ProductExaminePlan":
			return "SELECT No,KHBM as 客户编码,KHMC as 客户名称,CPLX as 产品, LJH as 零件号,LJMC as 零件名称, KHLJH AS 客户物料编码, ZDYLJMC AS 自定义零件名称 FROM QMS_ProductCustomerGroup";
		case "App_ProductExaminePlanFull":
			return "select KHBM, KHMC,CPLX, LJH, LJMC,KHLJH, ZDYLJMC from QMS_ProductCustomerGroup where No = '" + key + "'";

		// 售后模块
		case "App_After_Customer":
			return "SELECT distinct No ,KHMC as 客户名称,KHGC as 客户工厂,KHSF AS 客户省份 FROM QMS_AfterCustomer";
		case "App_After_CustomerFull":
			return "select KHMC, KHGC, KHSF from QMS_AfterCustomer where No = '" + key + "'";

		// 产品审核模块
		case "App_Product_Category":
			return "SELECT distinct No ,name as 产品类型 FROM qms_knowledgetype";
		case "App_Product_CategoryFull":
			return "select name as CPLX from qms_knowledgetype where No = '" + key + "'";

		// 获取人员
		case "App_User_Info":
			return "SELECT No, Name as 检验员 from Port_Emp";
		case "App_User_InfoFull":
			return "select Name as JYY from Port_Emp where No = '" + key + "'";
		default:
			break;
		}

		throw new Exception("err@没有判断的SQL标记:" + mark);
	}

	// public static String App_DataV_QMSSH(String mark, Hashtable ht) throws Exception {
	// String key = null;
	// if (ht != null && ht.containsKey("Key") == true)
	// key = ht.get("Key").toString();
	//
	// switch (mark)
	// {
	// case "App_DataV_QMSSH_Dept": //问题都有那些部门.
	// return "SELECT DISTINCT StrZeRenBuMenT FROM qms_problemtz WHERE StrZeRenBuMenT IS NOT NULL ORDER BY StrZeRenBuMenT";
	// //每个部门的各个总数量
	// case "App_DataV_QMSSH_Dept_Count":
	// return "SELECT COUNT(1) AS Count,StrZeRenBuMenT FROM qms_problemtz WHERE StrZeRenBuMenT IS NOT NULL GROUP BY StrZeRenBuMenT";
	// //各个部门重复的数据RepeateSta为1的都是重复的
	// case "App_DataV_QMSSH_Dept_Repeat_Count":
	// return "SELECT COUNT(1) AS Count,StrZeRenBuMenT FROM qms_problemtz WHERE StrZeRenBuMenT IS NOT NULL AND RepeateSta = 1 GROUP BY StrZeRenBuMenT";
	// //求平均值，问题总数/任务的个数=平均值
	// case "App_DataV_QMSSH_Dept_Avg":
	// return "SELECT StrZeRenBuMenT,CASE WHEN COUNT(DISTINCT GuanLianRenWuT) > 0 THEN ROUND(COUNT(*) / COUNT(DISTINCT GuanLianRenWuT), 2) ELSE 0 END AS sumavg FROM qms_problemtz WHERE StrZeRenBuMenT IS NOT NULL AND StrZeRenBuMenT != '' AND TRIM(StrZeRenBuMenT) != '' GROUP BY StrZeRenBuMenT ORDER BY sumavg DESC;";
	// default:
	// break;
	// }
	// throw new Exception("err@没有判断的SQL标记:" + mark);
	// }

}
